from time import asctime, strftime
from traceback import print_exc
import pymysql
from sys import exit


class OperateDatabase:
    def __init__(self, setting):
        # self.setting=setting
        self.con = pymysql.connect(host=setting['host'],
                                   user=setting['user'],
                                   password=setting['password'],
                                   port=setting['port'],
                                   charset=setting['charset'],
                                   database=setting['database'],
                                   autocommit=setting['autocommit'])

    def show_result(self, cursor):
        # data = cursor.fetchone()#查看单体信息
        data = cursor.fetchall()  # 查看多体信息
        # print(data)
        # sql="set character_set_results='GBK';"
        result = str(data).replace('(\'', '').replace('\',)', '').replace('(', '').replace(')', '')
        print(result)
        return data  # result
        # print(cursor.description)

    def perform(self, sql, err_info=None):
        # with中的代码执行完成后，会自动关闭文件；
        # 如果with语句中没有做try…except异常处理，出现错误时会直接将异常交给Python解释器抛出，不会自动关闭文件
        # 如果做了try…except异常处理，出现异常时，会自动关闭文件，再抛出异常
        with self.con.cursor() as cursor:
            try:
                cursor.execute(sql)
                result = self.show_result(cursor)
                return result
            except Exception as e:
                self.con.rollback()
                print(e)
                # print_exc()
                if err_info:
                    if type(err_info) == list:
                        for err in err_info:
                            print(err)
                    else:
                        print(err_info)

                # msg = f"ERROR - {self.setting['host']} session init failed: {e}" + "\n"
                # raise Exception(msg)

    def show_databases(self):
        with self.con.cursor() as cursor:
            cursor.execute('show databases;')
            self.show_result(cursor)

    def use_database(self, database):
        with self.con.cursor() as cursor:
            cursor.execute(f'use {database}')
            self.show_result(cursor)

    def create_database(self, database):
        err_info = f'创建数据库{database}失败'
        self.perform(f'CREATE DATABASE {database};', err_info)  # CREATE DATABASE IF NOT EXISTS {database}不会显示报错

    def drop_database(self, database, warn=True):
        if warn:
            while True:
                opt = input(f'是否删除数据库{database}(Y/N):')
                if opt == 'N':
                    print(f'已取消删除数据库{database}')
                    exit()
                elif opt == 'Y':
                    print(f'开始删除数据库{database}')
                    break
                else:
                    print('回答错误，请重新回答')

        err_info = [f'删除数据库{database}失败', f'请确认数据库{database}是否存在']
        self.perform(f'drop database {database};', err_info)  # 不报错drop database if exists {database};

    def show_tables(self):
        err_info = ['查看数据表失败', '请检查是否已选择数据库']
        self.perform('show tables', err_info)

    def show_table(self, table):
        # 查看表结构
        err_info = [f'查看数据表{table}失败', '请检查是否已选择数据库']
        self.perform(f'desc {table}', err_info)

    def show_size(self):
        # 查看表结构
        err_info = [f'查看数据表容量失败', '请检查是否已选择数据库']
        sql = '''SELECT
            table_schema AS '数据库',
            sum( table_rows ) AS '记录数',
            sum(
            TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
            sum(
            TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
        FROM
            information_schema.TABLES 
        GROUP BY
            table_schema 
        ORDER BY
            sum( data_length ) DESC,
            sum( index_length ) DESC;'''  # select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables;
        self.perform(sql, err_info)

    def create_table(self, table):
        err_info = '创建数据表失败'
        if table == 'history':
            sql = f'''create table {table}(
                            ds datetime not null comment '日期',
                            moisture_content float(4,3) default null comment '含水率',
                            tender_bud_num float(4,3) default null comment '现嫩芽数,原嫩芽率tender_bud_rate',
                            air_temperature float(3,1) default null comment '空气温度(°C)',
                            air_humidity float(4,3) default null comment '空气湿度',

                            primary key(ds) using btree)
                            engine=InnoDB default charset=utf8mb4;
                     '''  # update_time datetime default null comment '数据最后更新时间',
        elif table == 'user':
            sql = f'''create table {table}(
                            id int(11) not null key auto_increment,
                            update_time datetime default null comment '数据最后更新时间',
                            account int(11) default null comment '账号',
                            password int(11) default null comment '密码'
                             )
                            engine=InnoDB default charset=utf8mb4;
                     '''  # primary key(id)

        self.perform(sql, err_info)

    def drop_table(self, table, warn=False):
        if warn:
            while True:
                opt = input(f'是否删除数据表{table}(Y/N):')
                if opt == 'N':
                    print(f'已取消删除数据表{table}')
                    exit()
                elif opt == 'Y':
                    print(f'开始删除数据表{table}')
                    break
                else:
                    print('回答错误，请重新回答')
        err_info = ['删除数据表出错']
        self.perform(f'drop table {table}', err_info)

    def insert_history(self, datas):
        sql = "insert into history values(%s,%s,%s,%s,%s)"
        with self.con.cursor() as cursor:
            try:
                print(f'{asctime()}开始插入历史数据')
                cursor.execute(sql, datas)
                # self.con.commit()
            except:
                print_exc()

    def update_history(self, history):
        sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        with self.con.cursor() as cursor:
            try:
                print(f'{asctime()}开始更新历史数据')
                for k, v in history.items():
                    cursor.execute(sql, [k, v.get('confirm'), v.get('confirm_add'), v.get('suspect'),
                                         v.get('suspect_add'), v.get('heal'), v.get('heal_add'),
                                         v.get('dead'), v.get('dead_add')])
                # self.con.commit()
                print(f'{asctime}历史数据更新完毕')
            except:
                print_exc()

    def get_history_data(self, num=6):
        # sql='''select ds, moisture_content, tender_bud_rate, air_temperature, air_humidity from history'''
        sql = f'''SELECT * FROM(SELECT * FROM history data1
                  ORDER BY data1.ds DESC LIMIT {num})data2
                  ORDER BY data2.ds;'''
        return self.perform(sql, '展示历史数据')

    def check_processes(self):
        # 查看进程
        self.perform('show processlist;')

    def kill_process(self, process):
        # 关闭进程
        self.perform(f'kill {process};')

    def close(self):
        self.con.close()

    def generate_account(self):
        # sql1="SELECT SUBSTR(date_format(NOW(3),'%Y%m%d%H%i%s%f'),1,17) as now;"
        # SUBSTR(str, pos, len) 函数的参数 pos 的坐标从 1 开始；
        # %Y 四位数字表示的年份；而 %y 两位数字表示的年份；
        # %m 两位数字表示的月份（01, 02, ..., 12）；
        # %d 两位数字表示月中的天数（00, 01,..., 31）；
        # %f 可以获得6位的微秒 microsecond；低版本只显示000
        # 年份4位,月份2位,天数2位,时分秒6位,毫秒保留3位,总位数为17位；
        # sql2="select round(rand()*1000,0) as num;"
        sql = "select concat((select CAST((SELECT SUBSTR(date_format(NOW(3),'%Y%m%d%H%i%s%f'),1,17)) as CHAR)),(select CAST((select round(rand()*1000,0)) as CHAR)));"
        # self.perform(sql1,'生成时间失败')
        # self.perform(sql2,'生成随机数失败')
        account = self.perform(sql, '生成账号失败')
        return account.replace(',', '')


# 按间距中的绿色按钮以运行脚本。
if __name__ == '__main__':
    setting = {
        'host': 'localhost',  # 10.0.24.5 mysql.sqlpub.com
        'user': 'root',  # teamonitor
        'password': '1234',  # 0249a15c3d72f4aa
        'port': 3306,
        'charset': 'utf8mb4',
        'database': '',  # 默认是None或''，可以对所有数据库操作
        'autocommit': True  # 提交，插入、删除和更新数据库才会生效
    }

    db = OperateDatabase(setting)
    # db.create_database('teamonitor')
    db.use_database('teamonitor')

    # db.create_table('history')
    # from util import generate_data
    # from time import sleep
    # for i in range(1):
    #     data=generate_data()
    #     print(data)
    #     db.insert_history(data)
    #     sleep(1)
    data_list = db.get_history_data(1)
    for datas in data_list:
        print(datas)

    # db.generate_account()
    # db.drop_table('history')
    # db.show_databases()
    # db.show_size()
    # db.insert_data('d')
    # db.use_database(database='teamonitor')#mysql
    # db.show_tables()
    # db.show_table('history')

    db.close()

# 按间距中的绿色按钮以运行脚本。
if __name__ == '__main__':
    setting={
      'host': 'localhost',# 10.0.24.5 mysql.sqlpub.com
      'user': 'root',#teamonitor
      'password': '1234',#0249a15c3d72f4aa
      'port': 3306,
      'charset': 'utf8mb4',
      'database': '',#默认是None或''，可以对所有数据库操作
      'autocommit': True#提交，插入、删除和更新数据库才会生效
    }

    db=OperateDatabase(setting)
    #db.create_database('teamonitor')
    db.use_database('teamonitor')

    #db.create_table('history')
    # from util import generate_data
    # from time import sleep
    # for i in range(1):
    #     data=generate_data()
    #     print(data)
    #     db.insert_history(data)
    #     sleep(1)
    data_list=db.get_history_data(1)
    for datas in data_list:
        print(datas)

    #db.generate_account()
    #db.drop_table('history')
    #db.show_databases()
    #db.show_size()
    #db.insert_data('d')
    #db.use_database(database='teamonitor')#mysql
    #db.show_tables()
    #db.show_table('history')

    db.close()
